package at.brandl.wwi;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;
import at.brandl.wwi.WhereWasIPoint.BoundaryEnum;

public class WhereWasIDbAdapter {

	public static final String KEY_ID = "_id";
	public static final String KEY_TIME = "time";
	public static final String KEY_LONGITUDE = "longX";
	public static final String KEY_LATITUDE = "latY";
	public static final String KEY_ACCURACY = "accuracy";
	public static final String KEY_BOUNDARY = "boundary";
	private static final String TAG = WhereWasIDbAdapter.class.getName();
	private static final int DATABASE_VERSION = 3;
	private static final String DATABASE_NAME = "wwi";
	private static final String DATABASE_TABLE = "points";
	private static final String DATABASE_CREATE = "create table " + //
			DATABASE_TABLE + " (" + //
			KEY_ID + " integer primary key autoincrement, " + //
			KEY_TIME + " integer not null, " + //
			KEY_LONGITUDE + " integer not null, " + //
			KEY_LATITUDE + " integer not null, " + //
			KEY_ACCURACY + " integer not null, " + //
			KEY_BOUNDARY + " text);";
	private final Context context;
	private DatabaseHelper dbHelper;
	private SQLiteDatabase db;

	public WhereWasIDbAdapter(Context context) {
		this.context = context;
	}

	public WhereWasIDbAdapter open() {
		dbHelper = new DatabaseHelper(context);
		db = dbHelper.getWritableDatabase();
		return this;
	}

	public void close() {
		dbHelper.close();
	}

	public long insertPoint(WhereWasIPoint point) {
		ContentValues values = new ContentValues();
		values.put(KEY_TIME, Long.valueOf(point.getTimeMinutes()));
		values.put(KEY_LATITUDE, Integer.valueOf(point.getLatitudeE6()));
		values.put(KEY_LONGITUDE, Integer.valueOf(point.getLongitudeE6()));
		values.put(KEY_ACCURACY, Integer.valueOf(point.getAccuracy()));
		values.put(KEY_BOUNDARY, point.getBoundary() == null ? null : point
				.getBoundary().getText());
		return db.insert(DATABASE_TABLE, null, values);
	}

	public Cursor getLastPoint() {
		return db.query(DATABASE_TABLE, new String[] { KEY_ID, KEY_TIME,
				KEY_LATITUDE, KEY_LONGITUDE, KEY_ACCURACY, KEY_BOUNDARY },
				null, null, null, null, KEY_TIME + " DESC LIMIT 1");
	}

	public Cursor fetchAllPoints() {
		return db.query(DATABASE_TABLE, new String[] { KEY_ID, KEY_TIME,
				KEY_LATITUDE, KEY_LONGITUDE, KEY_ACCURACY, KEY_BOUNDARY },
				null, null, null, null, KEY_TIME + " desc");
	}

	public Cursor getPointsBetween(long startTimeMinutes, long endTimeMinutes) {
		return db.query(DATABASE_TABLE, new String[] { KEY_ID, KEY_TIME,
				KEY_LATITUDE, KEY_LONGITUDE, KEY_ACCURACY, KEY_BOUNDARY },
				KEY_TIME + " >= " + startTimeMinutes + " and " + KEY_TIME
						+ " <= " + endTimeMinutes, null, null, null, KEY_TIME
						+ " desc");
	}

	public Cursor getPoint(long id) {
		Log.i("dbadapter", "get point " + id);
		return db.query(DATABASE_TABLE, new String[] { KEY_ID, KEY_TIME,
				KEY_LATITUDE, KEY_LONGITUDE, KEY_ACCURACY, KEY_BOUNDARY },
				KEY_ID + "=" + id, null, null, null, null, null);
	}

	public int deletePoint(long pointId) {
		return db.delete(DATABASE_TABLE, KEY_ID + "=" + pointId, null);
	}

	public int deleteAllPoints() {
		return db.delete(DATABASE_TABLE, null, null);
	}

	public static WhereWasIPoint convertToPoint(Cursor point) {
		return new WhereWasIPoint(
				point.getLong(point.getColumnIndex(WhereWasIDbAdapter.KEY_ID)),
				point.getInt(point.getColumnIndex(WhereWasIDbAdapter.KEY_TIME)),
				point.getInt(point
						.getColumnIndex(WhereWasIDbAdapter.KEY_LONGITUDE)),
				point.getInt(point
						.getColumnIndex(WhereWasIDbAdapter.KEY_LATITUDE)),
				point.getInt(point
						.getColumnIndex(WhereWasIDbAdapter.KEY_ACCURACY)),
				BoundaryEnum.valueOfText(point.getString(point
						.getColumnIndex(WhereWasIDbAdapter.KEY_BOUNDARY))));
	}

	private static class DatabaseHelper extends SQLiteOpenHelper {

		private static final String DATABASE_DROP = "DROP TABLE if exists "
				+ DATABASE_TABLE;

		public DatabaseHelper(Context context) {
			super(context, DATABASE_NAME, null, DATABASE_VERSION);
		}

		@Override
		public void onCreate(SQLiteDatabase db) {
			db.execSQL(DATABASE_CREATE);
		}

		@Override
		public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
			Log.w(TAG, "Upgrading database from version " + oldVersion + " to "
					+ newVersion + ", which will destroy all old data");
			db.execSQL(DATABASE_DROP);
			onCreate(db);
		}
	}

}
